package org.cjstudio.holypoopsgrs_protytpe.resource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.cjstudio.holypoopsgrs_protytpe.resource.Resources.Scoreboard.ScoreEntry;
import org.cjstudio.holypoopsgrs_protytpe.resource.Resources.Scoreboard.ScoreboardResource;


public class ScoreboardResourceMySQLImpl extends ScoreboardResource {
	
	@Override
	public void updateScore(Long id, Integer score) {
		String query = "INSERT INTO holypoopsgrs.scoreboard (uid, score, scoreDate) VALUES (" + id + ", " + score + ", now()) ON DUPLICATE KEY UPDATE score=IF(" + score + ">=score, " + score + ", score), scoreDate=IF(" + score + ">=score, now(), scoreDate);";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public ScoreEntry getScoreEntry(Long id) {
		ScoreEntry entry = null;
		
		String query = "SELECT uid, score, scoreDate FROM holypoopsgrs.scoreboard WHERE uid=" + id + ";";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs.first()) {
				entry = new ScoreEntry(){};
				entry.rank = null;
				entry.id = rs.getLong("uid");
				entry.score = rs.getInt("score");
				entry.scoreDate = rs.getDate("scoreDate");
			}
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return entry;
	}
	
	@Override
	public ScoreEntry getRankedScoreEntry(Long id) {
		ScoreEntry entry = null;
		
		String query = "SELECT count(*), score, scoreDate FROM holypoopsgrs.scoreboard WHERE score>=(SELECT score FROM holypoopsgrs.scoreboard WHERE uid=" + id + ");";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if(rs.first()) {
				entry =  new ScoreEntry(){};
				entry.id = id;
				entry.rank = rs.getLong(1);
				entry.score = rs.getInt("score");
				entry.scoreDate = rs.getDate("scoreDate");
			}
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return entry;
	}
	
	@Override
	public ScoreEntry updateScoreThenGetRankedScoreEntry(Long id, Integer score) {
		ScoreEntry entry = null;

		String setQuery = "INSERT INTO holypoopsgrs.scoreboard (uid, score, scoreDate) VALUES (" + id + ", " + score + ", now()) ON DUPLICATE KEY UPDATE score=IF(" + score + ">=score, " + score + ", score), scoreDate=IF(" + score + ">=score, now(), scoreDate);";
		System.out.println(setQuery);
		String getQuery = "SELECT count(*), score, scoreDate FROM holypoopsgrs.scoreboard WHERE score>=(SELECT score FROM holypoopsgrs.scoreboard WHERE uid=" + id + ");";
		System.out.println(getQuery);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			
			Statement setStmt = conn.createStatement();
			setStmt.executeUpdate(setQuery);
			setStmt.close();
			
			Statement getStmt = conn.createStatement();
			ResultSet rs = getStmt.executeQuery(getQuery);
			if(rs.first()) {
				entry = new ScoreEntry(){};
				entry.id = id;
				entry.rank = rs.getLong(1);
				entry.score = rs.getInt("score");
				entry.scoreDate = rs.getDate("scoreDate");
			}
			getStmt.close();
			
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return entry;	
	}

	@Override
	public List<ScoreEntry> getScoreEntriesInRange(int start, int end) {
		List<ScoreEntry> entries = null;
		
		String query = "SELECT uid, score, scoreDate FROM holypoopsgrs.scoreboard ORDER BY score DESC, scoreDate DESC LIMIT " + (start-1) + ", " + (end - start + 1) + ";";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			int i=0;
			if(rs.first()) {
				entries = new ArrayList<ScoreEntry>();
				do {
					ScoreEntry entry = new ScoreEntry(){};
					entry.rank = (long) (start + i++);
					entry.id = rs.getLong("uid");
					entry.score = rs.getInt("score");
					entry.scoreDate = rs.getDate("scoreDate");
					entries.add(entry);
				} while(rs.next());
			}
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return entries;
	}

	@Override
	public List<ScoreEntry> getLeaderEntries(int limit) {
		return getScoreEntriesInRange(1, limit);
	}
	
	/* TEST TOOL */
	public void delete(Long id) {
		String query = "DELETE FROM holypoopsgrs.scoreboard WHERE uid=" + id + ";";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void deleteAll() {
		String query = "DELETE FROM holypoopsgrs.scoreboard;";
		System.out.println(query);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public ScoreEntry setScoreThenGetRankedScoreEntry(Long id, Integer score) {
		ScoreEntry entry = null;

		String setQuery = "INSERT INTO holypoopsgrs.scoreboard (uid, score, scoreDate) VALUES (" + id + ", " + score + ", now()) ON DUPLICATE KEY UPDATE score=" + score + ", scoreDate=now();";
		System.out.println(setQuery);
		String getQuery = "SELECT count(*), score, scoreDate FROM holypoopsgrs.scoreboard WHERE score>=(SELECT score FROM holypoopsgrs.scoreboard WHERE uid=" + id + ");";
		System.out.println(getQuery);
		
		try {
			Connection conn = DriverManager.getConnection(MySQL.DB_URL, MySQL.DB_ID, MySQL.DB_PW);
			
			Statement setStmt = conn.createStatement();
			setStmt.executeUpdate(setQuery);
			setStmt.close();
			
			Statement getStmt = conn.createStatement();
			ResultSet rs = getStmt.executeQuery(getQuery);
			if(rs.first()) {
				entry = new ScoreEntry(){};
				entry.id = id;
				entry.rank = rs.getLong(1);
				entry.score = rs.getInt("score");
				entry.scoreDate = rs.getDate("scoreDate");
			}
			getStmt.close();
			
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return entry;	
	}

}
